/*
Created		7. 10. 2014
Modified		8. 11. 2014
Project		
Model			
Company		
Author		
Version		
Database		MS SQL 2005 
*/


if exists (select * from sys.tables where object_id = object_id('[receive_data]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
	Drop table [receive_data] 
go
if exists (select * from sys.tables where object_id = object_id('[production_data]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
	Drop table [production_data] 
go
if exists (select * from sys.tables where object_id = object_id('[production]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
	Drop table [production] 
go
if exists (select * from sys.tables where object_id = object_id('[recipe_step]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
	Drop table [recipe_step] 
go
if exists (select * from sys.tables where object_id = object_id('[ingredient]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
	Drop table [ingredient] 
go
if exists (select * from sys.tables where object_id = object_id('[recipe]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
	Drop table [recipe] 
go
if exists (select * from sys.tables where object_id = object_id('[log]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
	Drop table [log] 
go


Create table [log]
(
	[id_log] Uniqueidentifier Default NewSequentialId() NOT NULL, UNIQUE ([id_log]),
	[type] Nvarchar(3) NULL,
	[description] Nvarchar(1024) NULL,
	[x_created] Datetime Default GetUTCDate() NULL,
Primary Key ([id_log])
) 
go

Create table [recipe]
(
	[id_recipe] Uniqueidentifier Default NewSequentialId() NOT NULL, UNIQUE ([id_recipe]),
	[identifier] Nvarchar(128) NULL,
	[description] Nvarchar(1024) NULL,
	[x_created] Datetime Default GetUTCDate() NULL,
	[x_modified] Datetime NULL,
Primary Key ([id_recipe])
) 
go

Create table [ingredient]
(
	[id_ingredient] Uniqueidentifier Default NewSequentialId() NOT NULL, UNIQUE ([id_ingredient]),
	[identifier] Nvarchar(128) NULL,
	[description] Nvarchar(1024) NULL,
	[value] Float NULL,
	[x_created] Datetime Default GetUTCDate() NULL,
	[x_modified] Datetime NULL,
Primary Key ([id_ingredient])
) 
go

Create table [recipe_step]
(
	[id_recipe_step] Uniqueidentifier Default NewSequentialId() NOT NULL, UNIQUE ([id_recipe_step]),
	[id_recipe] Uniqueidentifier Default NewSequentialId() NOT NULL,
	[id_ingredient] Uniqueidentifier Default NewSequentialId() NOT NULL,
	[step_number] Integer NULL,
	[weight] Float NULL,
	[humidity] Float NULL,
	[x_created] Datetime Default GetUTCDate() NULL,
	[x_modified] Datetime NULL,
Primary Key ([id_recipe_step])
) 
go

Create table [production]
(
	[id_production] Uniqueidentifier Default NewSequentialId() NOT NULL, UNIQUE ([id_production]),
	[id_recipe_step] Uniqueidentifier Default NewSequentialId() NOT NULL,
	[weight] Float NULL,
	[humidity] Float NULL,
	[x_created] Datetime Default GetUTCDate() NULL,
Primary Key ([id_production])
) 
go

Create table [production_data]
(
	[id_production_data] Uniqueidentifier Default NewSequentialId() NOT NULL, UNIQUE ([id_production_data]),
	[id_production] Uniqueidentifier Default NewSequentialId() NOT NULL,
	[data] Varbinary(max) NULL,
	[x_created] Datetime Default GetUTCDate() NULL,
Primary Key ([id_production_data])
) 
go

Create table [receive_data]
(
	[id_receive_data] Uniqueidentifier Default NewSequentialId() NOT NULL, UNIQUE ([id_receive_data]),
	[identification] Char(1) NULL,
	[step_number] Integer NULL,
	[recipe_name] Nvarchar(16) NULL,
	[recipe_cement] Integer NULL,
	[recipe_struska] Integer NULL,
	[recipe_kamenivo1] Integer NULL,
	[recipe_kamenivo2] Integer NULL,
	[recipe_kamenivo3] Integer NULL,
	[recipe_kamenivo4] Integer NULL,
	[recipe_kamenivo5] Integer NULL,
	[recipe_prisady] Integer NULL,
	[recipe_rezerva] Integer NULL,
	[rezerva1] Integer NULL,
	[rezerva2] Integer NULL,
	[prumer_vlhkost3] Integer NULL,
	[prumer_vlhkost4] Integer NULL,
	[navyseni3] Integer NULL,
	[navyseni4] Integer NULL,
	[rezim_k3] Integer NULL,
	[rezim_k4] Integer NULL,
	[rezerva3] Integer NULL,
	[rezerva4] Integer NULL,
	[rezerva5] Integer NULL,
	[rezerva6] Integer NULL,
	[cement_silo1] Integer NULL,
	[cement_silo2] Integer NULL,
	[struska_silo3] Integer NULL,
	[cement_silo4] Integer NULL,
	[cement_silo5] Integer NULL,
	[kamenivo_silo1] Integer NULL,
	[kamenivo_silo2] Integer NULL,
	[kamenivo_silo3] Integer NULL,
	[kamenivo_silo4] Integer NULL,
	[kamenivo_silo5] Integer NULL,
	[prisady] Integer NULL,
	[cement_struska_zbytek] Integer NULL,
	[kamenivo_zbytek] Integer NULL,
	[rezerva7] Integer NULL,
	[rezerva8] Integer NULL,
	[rezerva9] Integer NULL,
	[rezerva10] Integer NULL,
	[x_created] Datetime Default GetUTCDate() NULL,
Primary Key ([id_receive_data])
) 
go


Alter table [recipe_step] add  foreign key([id_recipe]) references [recipe] ([id_recipe])  on update no action on delete no action 
go
Alter table [recipe_step] add  foreign key([id_ingredient]) references [ingredient] ([id_ingredient])  on update no action on delete no action 
go
Alter table [production] add  foreign key([id_recipe_step]) references [recipe_step] ([id_recipe_step])  on update no action on delete no action 
go
Alter table [production_data] add  foreign key([id_production]) references [production] ([id_production])  on update no action on delete no action 
go


Set quoted_identifier on
go


Set quoted_identifier off
go


